IF EXISTS (SELECT*
  FROM sysobjects
  WHERE Id=OBJECT_ID( N'[dbo].[factHotelDetailedRevenue]' )
		AND OBJECTPROPERTY( Id, N'IsProcedure' )=1)
BEGIN
DROP PROCEDURE [dbo].[factHotelDetailedRevenue]
END
;

IF EXISTS (SELECT*
  FROM sysobjects
  WHERE Id=OBJECT_ID( N'[dbo].[dimextMergedHotelRooms]' )
		AND OBJECTPROPERTY( Id, N'IsProcedure' )=1)
BEGIN
DROP PROCEDURE [dbo].[dimextMergedHotelRooms]
END
;

IF EXISTS (SELECT*
  FROM sysobjects
  WHERE Id=OBJECT_ID( N'[dbo].[factHotelsRoomAvailability]' )
		AND OBJECTPROPERTY( Id, N'IsProcedure' )=1)
BEGIN
DROP PROCEDURE [dbo].[factHotelsRoomAvailability]
END
;

IF EXISTS (SELECT*
  FROM sysobjects
  WHERE Id=OBJECT_ID( N'[dbo].[factHotelRoomBlockedEntry]' )
		AND OBJECTPROPERTY( Id, N'IsProcedure' )=1)
BEGIN
DROP PROCEDURE [dbo].[factHotelRoomBlockedEntry]
END
;

IF EXISTS (SELECT*
  FROM sysobjects
  WHERE Id=OBJECT_ID( N'[dbo].[factHotelReservationEntry]' )
		AND OBJECTPROPERTY( Id, N'IsProcedure' )=1)
BEGIN
DROP PROCEDURE [dbo].[factHotelReservationEntry]
END
;

IF EXISTS (SELECT*
  FROM sysobjects
  WHERE Id=OBJECT_ID( N'[dbo].[factHotelGuestList]' )
		AND OBJECTPROPERTY( Id, N'IsProcedure' )=1)
BEGIN
DROP PROCEDURE [dbo].[factHotelGuestList]
END
;


/****** Object:  StoredProcedure [dbo].[factHotelDetailedRevenue]    Script Date: 24-Jan-23 9:42:24 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[factHotelDetailedRevenue] 

	@RevEntryRowId bigint,
	@ReservationRowId bigint,
	@RevEntryNewRowId bigint,
	@ReservationNewRowId bigint

AS
BEGIN
SET NOCOUNT ON;
-- Dismiss selecting from source if no rows will be fetched
IF(
SELECT COUNT(*) FROM [stg$Detailed Revenue Entry] sDRE
where sDRE.bigint_timestamp BETWEEN @RevEntryRowId AND @RevEntryNewRowId) = 0
AND
(SELECT COUNT(*) FROM [stg$Hotel Reservation] sHR
where sHR.bigint_timestamp BETWEEN @ReservationRowId AND @ReservationNewRowId) = 0
BEGIN
SELECT 
	'RowCount' = @@rowcount
END
ELSE
BEGIN

WITH 

/*
Get a list of all reservations that have changed
*/

tReservationsChangedList
AS
(SELECT [Reservation No_] FROM [stg$Detailed Revenue Entry] sDRE
where sDRE.bigint_timestamp BETWEEN @RevEntryRowId AND @RevEntryNewRowId
UNION
SELECT [Reservation No_] FROM [stg$Hotel Reservation] sHR
where sHR.bigint_timestamp BETWEEN @ReservationRowId AND @ReservationNewRowId
),

/*
Get the list of companies used
*/
tCompanies
AS
(SELECT
		dCOM.[SK_Company] as [Company]
	   ,dCOM.[CompanyPrefix]
	FROM [DW].[dCompany] dCOM
	WHERE dCOM.[SK_Company] <> -1),
/*
Set up dimensions and support tables used with the main data process
*/

tHotelRooms
AS
(SELECT dHR.Room,tCOM.CompanyPrefix, dHR.SK_HotelRooms from DW.dHotelRooms dHR
LEFT OUTER JOIN [tCompanies] tCOM
		ON dHR.[Company] = tCOM.[Company]),

tHotelRoomsType
AS
(SELECT dHR.[Room Type],tCOM.CompanyPrefix, dHR.SK_HotelRooms 
	from DW.dHotelRooms dHR
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dHR.[Company] = tCOM.[Company]
	WHERE dHR.[Room] = dHR.[Room Type]),

tLocation
AS
(SELECT
		dLOC.[SK_location]
	   ,tCOM.[CompanyPrefix]
	   ,dLOC.[LocationCode]
	FROM DW.[dLocation] dLOC
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dLOC.[Company] = tCOM.[Company]),

tVendor
AS
(
SELECT dVEN.[Company]
		,dVEN.VendorNo
		,dVEN.SK_Vendor
from DW.dVendor dVEN

),

tCustomer 
AS
(SELECT 
	dCUST.[SK_Customer]
	,tCOM.CompanyPrefix
	,dCUST.[CustomerNo]
	FROM [DW].[dCustomer] dCUST
	LEFT OUTER JOIN tCompanies tCOM
		ON dCUST.[Company] = tCOM.[Company]
),

tItem
AS
(SELECT
		dITM.[SK_Item]
	   ,tCOM.[CompanyPrefix]
	   ,dITM.[No]
	   ,dVEN.SK_Vendor
	   ,dITM.ValidFrom
	   ,dITM.ValidTo
	FROM [DW].[dItem] dITM
	LEFT OUTER JOIN tCompanies tCOM
		ON dITM.[Company] = tCOM.[Company]
	LEFT OUTER JOIN tVendor dVEN
		ON dITM.Company = dVEN.[Company]
		AND dITM.VendorNo = dVEN.VendorNo
		),
tHotelDetailRevenue AS(
SELECT
	COALESCE(COM.[SK_Company], -1) AS [SK_Company]
   ,sDRE.[bigint_timestamp] as [RowId]
   ,COALESCE(tITM.SK_Item, -1) AS SK_Item
   ,COALESCE(dLoc.SK_Location, -1) AS SK_Location
   ,COALESCE(dHR.SK_HotelRooms, tHRT.SK_HotelRooms, -1) AS SK_HotelRoom
   ,tCUST.SK_Customer
   ,sDRE.[Entry No_]
   ,sDRE.[Reservation No_]
   ,sDRE.[Transaction Type]
   ,sDRE.[Activity Internal Status]
   ,sDRE.[Revenue Type]
   ,sDRE.[Paid]
   ,sDRE.[Included in Rate]
   ,sDRE.[Quantity]
   ,sDRE.[Amount]
   ,sDRE.[Date]
   ,sHR.[Reservation Channel] AS [Reservation Channel]
   ,sHR.[Market Segment] AS [Market Segment]
   ,sHR.[Status]
FROM [dbo].[stg$Detailed Revenue Entry] sDRE
LEFT JOIN [dbo].[stg$Hotel Reservation] sHR
ON sDRE.CompanyPrefix = sHR.CompanyPrefix
AND sDRE.[Reservation No_] = sHR.[Reservation No_]
LEFT JOIN tHotelRooms dHR
ON sHR.[Room No_] = dHR.Room
AND sHR.CompanyPrefix = dHR.CompanyPrefix
LEFT JOIN tHotelRoomsType tHRT
ON sHR.[CompanyPrefix] = tHRT.CompanyPrefix
AND sHR.[Room Type] = tHRT.[Room Type]
LEFT JOIN tLocation dLoc
	ON COALESCE(nullif(sDRE.[Store No_],''),sDRE.Property) = dLoc.LocationCode
	AND sDRE.CompanyPrefix = dLoc.CompanyPrefix
LEFT JOIN DW.dCompany COM
	ON sDRE.CompanyPrefix = COM.CompanyPrefix
LEFT JOIN tCustomer tCUST
	ON sDRE.[Customer No_] = tCUST.CustomerNo
	AND sDRE.[CompanyPrefix] = tCUST.CompanyPrefix
LEFT JOIN tItem tITM
ON sDRE.[Item No_] = tITM.No
AND sDRE.CompanyPrefix = tITM.CompanyPrefix
AND sDRE.[Date] >= tITM.ValidFrom AND sDRE.[Date] <= tITM.ValidTo 
WHERE sDRE.[Reservation No_] in (SELECT [Reservation No_] from tReservationsChangedList)
),

tHotelDetailRevenueRanked AS ( SELECT
		*
		,ROW_NUMBER() OVER (PARTITION BY [SK_Company],[Entry No_],[Reservation No_] ORDER BY [RowID] DESC) AS [Rank]
	FROM tHotelDetailRevenue)

MERGE [DW].[fHotelDetailedRevenue] AS TARGET USING
	(SELECT
	tHDRR.[SK_Company]
	,tHDRR.[SK_Item]
	,tHDRR.[SK_Location]
	,tHDRR.[SK_HotelRoom]
	,tHDRR.[SK_Customer]
	,tHDRR.[Entry No_]
	,tHDRR.[Reservation No_]
	,tHDRR.[Transaction Type]
	,tHDRR.[Activity Internal Status]
	,tHDRR.[Revenue Type]
	,tHDRR.[Paid]
	,tHDRR.[Included in Rate]
	,tHDRR.[Quantity]
	,tHDRR.[Amount]
	,tHDRR.[Date]
	,tHDRR.[Reservation Channel]
	,tHDRR.[Market Segment]
	,tHDRR.[Status]
	FROM tHotelDetailRevenueRanked tHDRR
		WHERE tHDRR.[Rank] = 1) AS SOURCE
	ON
	TARGET.[SK_Company] = SOURCE.[SK_Company]
	AND TARGET.[Entry No_] = SOURCE.[Entry No_]
	AND TARGET.[Reservation No_] = SOURCE.[Reservation No_]
WHEN MATCHED
	THEN UPDATE
		SET [SK_HotelRoom] = SOURCE.[SK_HotelRoom]
	,[SK_Location] = SOURCE.[SK_Location]
	,[SK_Item] = SOURCE.[SK_Item]
	,[SK_Customer] = SOURCE.[SK_Customer]
	,[Transaction Type] = SOURCE.[Transaction Type]
	,[Activity Internal Status] = SOURCE.[Activity Internal Status]
	,[Revenue Type] = SOURCE.[Revenue Type]
	,[Paid] = SOURCE.[Paid]
	,[Included in Rate] = SOURCE.[Included in Rate]
	,[Quantity] = SOURCE.[Quantity]
	,[Amount] = SOURCE.[Amount]
	,[Date] = SOURCE.[Date]
	,[Reservation Channel] = SOURCE.[Reservation Channel]
	,[Market Segment] = SOURCE.[Market Segment]
	,[Status] = SOURCE.[Status]

	WHEN NOT MATCHED BY TARGET
	THEN INSERT
		([SK_Company]
	,[SK_Item]
	,[SK_Location]
	,[SK_HotelRoom]
	,[SK_Customer]
	,[Entry No_]
	,[Reservation No_]
	,[Transaction Type]
	,[Activity Internal Status]
	,[Revenue Type]
	,[Paid]
	,[Included in Rate]
	,[Quantity]
	,[Amount]
	,[Date]
	,[Reservation Channel]
	,[Market Segment]
	,[Status])
	VALUES
	(SOURCE.[SK_Company]
	,SOURCE.[SK_Item]
	,SOURCE.[SK_Location]
	,SOURCE.[SK_HotelRoom]
	,SOURCE.[SK_Customer]
	,SOURCE.[Entry No_]
	,SOURCE.[Reservation No_]
	,SOURCE.[Transaction Type]
	,SOURCE.[Activity Internal Status]
	,SOURCE.[Revenue Type]
	,SOURCE.[Paid]
	,SOURCE.[Included in Rate]
	,SOURCE.[Quantity]
	,SOURCE.[Amount]
	,SOURCE.[Date]
	,SOURCE.[Reservation Channel]
	,SOURCE.[Market Segment]
	,SOURCE.[Status])

	WHEN NOT MATCHED BY SOURCE
	THEN DELETE
;
END

SELECT
	'RowCount' = @@rowcount
END


GO

/****** Object:  StoredProcedure [dbo].[factHotelGuestList]    Script Date: 20-Jan-23 10:15:20 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO






-- =============================================
-- Author:		Gisli Olafsson
-- Modified Date: 2023-01-19
-- Description:	SP for hotels guest list report
-- =============================================

CREATE PROCEDURE [dbo].[factHotelGuestList] 

	@ResGuestRowId bigint,
	@ReservationRowId bigint,
	@ResGuestNewRowId bigint,
	@ReservationNewRowId bigint

AS
BEGIN
SET NOCOUNT ON;
	-- Dismiss selecting from source if no rows will be fetched
	IF ( SELECT
		COUNT(*)
	FROM [stg$Hotel Res_ Guestlist] sHRG
	WHERE sHRG.bigint_timestamp BETWEEN @ResGuestRowId AND @ResGuestNewRowId)
= 0
AND (SELECT
		COUNT(*)
	FROM [stg$Hotel Reservation] sHR
	WHERE sHR.bigint_timestamp BETWEEN @ReservationRowId AND @ReservationNewRowId)
= 0
BEGIN
SELECT
	'RowCount' = @@rowcount
END
ELSE
BEGIN
WITH tReservationsChangedList
AS
(SELECT
		[Reservation No_]
	FROM [stg$Hotel Res_ Guestlist] sHRG
	WHERE sHRG.bigint_timestamp BETWEEN @ResGuestRowId AND @ResGuestNewRowId
	UNION
	SELECT
		[Reservation No_]
	FROM [stg$Hotel Reservation] sHR
	WHERE sHR.bigint_timestamp BETWEEN @ReservationRowId AND @ReservationNewRowId),

/*
  Get the list of companies used
  */
tCompanies
AS
(SELECT
		dCOM.[SK_Company] AS [Company]
	   ,dCOM.[CompanyPrefix]
	FROM [DW].[dCompany] dCOM
	WHERE dCOM.[SK_Company] <> -1),
/*
  Set up dimensions and support tables used with the main data process
  */

tHotelRoomsType
AS
(SELECT
		dHR.[Room Type]
	   ,tCOM.CompanyPrefix
	   ,dHR.SK_HotelRooms
	FROM DW.dHotelRooms dHR
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dHR.[Company] = tCOM.[Company]
	WHERE dHR.[Room] = dHR.[Room Type]),

tCustomer
AS
(SELECT
		dCUST.[SK_Customer]
	   ,tCOM.CompanyPrefix
	   ,dCUST.[CustomerNo]
	FROM [DW].[dCustomer] dCUST
	LEFT OUTER JOIN tCompanies tCOM
		ON dCUST.[Company] = tCOM.[Company]),

tLocation
AS
(SELECT
		dLOC.[SK_Location]
	   ,tCOM.[CompanyPrefix]
	   ,dLOC.[LocationCode]
	FROM DW.[dLocation] dLOC
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dLOC.[Company] = tCOM.[Company]),

tHotelGuestList
AS
(SELECT
		sHRG.[Reservation No_]
	   ,tCOM.Company
	   ,sHR.[Arrival Date]
	   ,sHR.[Departure Date]
	   ,sHRG.[Guest Name]
	   ,sHRG.[Guest No_]
	   ,tCUST.SK_Customer
	   ,sHR.Status
	   ,tLOC.SK_Location
	   ,dM.AccountNo
	   ,IIF(dM.Country <> '', dM.Country, sHR.Country) AS Country
	   ,[Guest Type]
	   ,sHGG.GuestGroupType
	   ,sHGG.Description
	   ,sHR.[Market Segment]
	   ,sHR.[Reservation Channel]
	   ,tHRT.SK_HotelRooms
	   ,CONCAT(sHRG.[Reservation No_], sHRG.[Sequence]) AS 'GuestId'
	   ,sHRG.bigint_timestamp AS [RowId]
	FROM [stg$Hotel Res_ Guestlist] sHRG
	LEFT JOIN DW.dMember dM
		ON sHRG.[Guest No_] = dM.AccountNo
	JOIN [stg$Hotel Reservation] sHR
		ON sHR.[Reservation No_] = sHRG.[Reservation No_]
		AND sHR.[CompanyPrefix] = sHRG.[CompanyPrefix]
	JOIN [stg$Hotel Guest Group] sHGG
		ON sHGG.CompanyPrefix = sHRG.CompanyPrefix
		AND sHGG.GuestGroup = sHRG.[Guest Type]
	JOIN tHotelRoomsType tHRT
		ON tHRT.[Room Type] = sHR.[Room Type]
	JOIN tCompanies tCOM
		ON tCOM.CompanyPrefix = sHR.CompanyPrefix
	JOIN tLocation tLOC
		ON tLOC.CompanyPrefix = sHRG.CompanyPrefix
		AND tLOC.LocationCode = sHR.Property
	LEFT JOIN tCustomer tCUST
		ON tCUST.CompanyPrefix = sHRG.CompanyPrefix
		AND tCUST.CustomerNo = sHR.[Customer No_]
	WHERE sHRG.[Reservation No_] IN (SELECT
			[Reservation No_]
		FROM tReservationsChangedList)),

tHotelGuestListRanked
AS
(SELECT
		*
	   ,ROW_NUMBER() OVER (PARTITION BY [Company], [Reservation No_], [GuestId] ORDER BY [RowID] DESC) AS [Rank]
	FROM tHotelGuestList)


MERGE [DW].[fHotelGuestList] AS TARGET USING (SELECT
		tHGR.[Reservation No_]
	   ,tHGR.[Company] AS [SK_Company]
	   ,tHGR.[Arrival Date]
	   ,tHGR.[Departure Date]
	   ,tHGR.[Guest Name]
	   ,tHGR.[Guest No_]
	   ,tHGR.[SK_Customer]
	   ,tHGR.[Status]
	   ,tHGR.[SK_Location]
	   ,tHGR.[AccountNo]
	   ,tHGR.[Country]
	   ,tHGR.[Guest Type]
	   ,tHGR.[GuestGroupType]
	   ,tHGR.[Description]
	   ,tHGR.[Market Segment]
	   ,tHGR.[Reservation Channel]
	   ,tHGR.[SK_HotelRooms]
	   ,tHGR.[GuestId]
	FROM tHotelGuestListRanked tHGR
	WHERE tHGR.[rank] = 1) AS SOURCE
ON TARGET.[SK_Company] = SOURCE.SK_Company
	AND TARGET.[Reservation No_] = SOURCE.[Reservation No_]
	AND TARGET.[GuestId] = SOURCE.[GuestId]
WHEN MATCHED
	THEN UPDATE
		SET [Arrival Date] = SOURCE.[Arrival Date]
		   ,[Departure Date] = SOURCE.[Departure Date]
		   ,[Guest Name] = SOURCE.[Guest Name]
		   ,[Guest No_] = SOURCE.[Guest No_]
		   ,[SK_Customer] = SOURCE.[SK_Customer]
		   ,[Status] = SOURCE.[Status]
		   ,[SK_Location] = SOURCE.[SK_Location]
		   ,[AccountNo] = SOURCE.[AccountNo]
		   ,[Country] = SOURCE.[Country]
		   ,[Guest Type] = SOURCE.[Guest Type]
		   ,[GuestGroupType] = SOURCE.[GuestGroupType]
		   ,[Description] = SOURCE.[Description]
		   ,[Market Segment] = SOURCE.[Market Segment]
		   ,[Reservation Channel] = SOURCE.[Reservation Channel]
		   ,[SK_HotelRooms] = SOURCE.[SK_HotelRooms]
WHEN NOT MATCHED BY TARGET
	THEN INSERT ([Reservation No_]
		, [SK_Company]
		, [Arrival Date]
		, [Departure Date]
		, [Guest Name]
		, [Guest No_]
		, [SK_Customer]
		, [Status]
		, [SK_Location]
		, [AccountNo]
		, [Country]
		, [Guest Type]
		, [GuestGroupType]
		, [Description]
		, [Market Segment]
		, [Reservation Channel]
		, [SK_HotelRooms]
		, [GuestId])
			VALUES ([SOURCE].[Reservation No_], [SOURCE].[SK_Company], [SOURCE].[Arrival Date], [SOURCE].[Departure Date], [SOURCE].[Guest Name], [SOURCE].[Guest No_], [SOURCE].[SK_Customer], [SOURCE].[Status], [SOURCE].[SK_Location], [SOURCE].[AccountNo], [SOURCE].[Country], [SOURCE].[Guest Type], [SOURCE].[GuestGroupType], [SOURCE].[Description], [SOURCE].[Market Segment], [SOURCE].[Reservation Channel], [SOURCE].[SK_HotelRooms], [SOURCE].[GuestId])
;
END

SELECT
	'RowCount' = @@rowcount
END
GO

/****** Object:  StoredProcedure [dbo].[factHotelReservationEntry]    Script Date: 20-Jan-23 10:15:29 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





-- =============================================
-- Author:		Gisli Olafsson
-- Modified Date: 2022-12-19
-- Description:	SP for hotels reservation entry report
-- Modified: 20/12/22 - Added Adult and child count on Max entry
--                    - Added new SK_PayHotelRooms key 
-- =============================================

CREATE PROCEDURE [dbo].[factHotelReservationEntry] 

	@ResEntryRowId bigint,
	@ReservationRowId bigint,
	@ResEntryNewRowId bigint,
	@ReservationNewRowId bigint

AS
BEGIN
SET NOCOUNT ON;
	-- Dismiss selecting from source if no rows will be fetched
	IF ( SELECT
		COUNT(*)
	FROM [stg$Hotel Res_ Entry] sHR
	WHERE sHR.bigint_timestamp BETWEEN @ResEntryRowId AND @ResEntryNewRowId)
= 0
AND (SELECT
		COUNT(*)
	FROM [stg$Hotel Reservation] sHR
	WHERE sHR.bigint_timestamp BETWEEN @ReservationRowId AND @ReservationNewRowId)
= 0
BEGIN
SELECT
	'RowCount' = @@rowcount
END
ELSE
BEGIN
WITH tReservationsChangedList
AS
(SELECT
		[Reservation No_]
	FROM [stg$Hotel Res_ Entry] sHRE
	WHERE sHRE.bigint_timestamp BETWEEN @ResEntryRowId AND @ResEntryNewRowId
	UNION
	SELECT
		[Reservation No_]
	FROM [stg$Hotel Reservation] sHR
	WHERE sHR.bigint_timestamp BETWEEN @ReservationRowId AND @ReservationNewRowId),

/*
  Get the list of companies used
  */
tCompanies
AS
(SELECT
		dCOM.[SK_Company] AS [Company]
	   ,dCOM.[CompanyPrefix]
	FROM [DW].[dCompany] dCOM
	WHERE dCOM.[SK_Company] <> -1),
/*
  Set up dimensions and support tables used with the main data process
  */

tHotelRooms
AS
(SELECT
		dHR.Room
	   ,tCOM.CompanyPrefix
	   ,dHR.SK_HotelRooms
	   ,dHR.Property
	FROM DW.dHotelRooms dHR
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dHR.[Company] = tCOM.[Company]),

tHotelRoomsType
AS
(SELECT
		dHR.[Room Type]
	   ,tCOM.CompanyPrefix
	   ,dHR.SK_HotelRooms
	FROM DW.dHotelRooms dHR
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dHR.[Company] = tCOM.[Company]
	WHERE dHR.[Room] = dHR.[Room Type]),
/*Added JI*/
tPHotelRoomsType
AS
(SELECT
		dHR.[Room Type]
	   ,tCOM.CompanyPrefix
	   ,dHR.SK_HotelRooms
	FROM DW.dHotelRooms dHR
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dHR.[Company] = tCOM.[Company]
	WHERE dHR.[Room] = dHR.[Room Type]),

tCustomer
AS
(SELECT
		dCUST.[SK_Customer]
	   ,tCOM.CompanyPrefix
	   ,dCUST.[CustomerNo]
	FROM [DW].[dCustomer] dCUST
	LEFT OUTER JOIN tCompanies tCOM
		ON dCUST.[Company] = tCOM.[Company]),

tLocation
AS
(SELECT
		dLOC.[SK_Location]
	   ,tCOM.[CompanyPrefix]
	   ,dLOC.[LocationCode]
	FROM DW.[dLocation] dLOC
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dLOC.[Company] = tCOM.[Company]),
/*Added JI*/
tHotelResEntryMax
AS
(SELECT
		sHRE.CompanyPrefix
	   ,sHRE.[Property]
	   ,sHRE.[Reservation No_]
	   ,MAX(sHRE.[Entry No_]) AS [MaxEntry]
	   ,Adults
	   ,Children
	FROM [dbo].[stg$Hotel Res_ Entry] sHRE
	GROUP BY [Reservation No_]
			,Adults
			,Children
			,Property
			,CompanyPrefix),

tHotelReservationEntry
AS
(SELECT
		dCOM.SK_Company
	   ,sHRE.[Date]
	   ,sHRE.[Reservation No_]
	   ,sHRE.[Entry No_]
	   ,CAST(sHRE.[Arrival Date] AS DATE) AS [Arrival Date]
	   ,CAST(sHRE.[Departure Date] AS DATE) AS [Departure Date]
	   ,CAST(sHRE.[$systemCreatedAt] AS DATE) AS [Booking Date]
	   ,CAST(sHR.[Cancelation DateTime] AS DATE) AS [Cancelation DateTime]
	   ,CASE sHR.[Cancelation DateTime]
			WHEN '1753-01-01' THEN DATEDIFF("day", sHR.[$systemCreatedAt], sHR.[Arrival Date])
			ELSE DATEDIFF("day", sHR.[$systemCreatedAt], sHR.[Cancelation DateTime])
		END AS DaysFromBookingToArrivalOrCancel
	   ,tCUST.SK_Customer
	   ,COALESCE(dLoc.SK_Location, -1) AS SK_Location
	   ,COALESCE(tHR.SK_HotelRooms, tHRT.SK_HotelRooms, -1) AS SK_HotelRooms
	   ,COALESCE(tPHRT.SK_HotelRooms, -1) AS SK_PayHotelRooms---Added JI
	   ,sHRE.[No_ of Rooms]
	   ,sHRE.[Currency]
	   ,sHRE.[Country]
	   ,COALESCE(sHR.[Market Segment], sHRE.[Segment], '') AS [Market Segment]
	   ,COALESCE(sHR.[Reservation Channel], sHRE.[Reservation Channel], '') AS [Reservation Channel]
	   ,sHRE.[Reservation Type] AS [Reservation Type Description]
	   ,sHRE.[Status]
	   ,CASE sHRE.[Status]
			--WHEN 0 THEN 'Draft'
			WHEN 1 THEN 'Confirmed'
			WHEN 2 THEN 'In House'
			WHEN 3 THEN 'Checked Out'
			WHEN 4 THEN 'Cancelled'
			WHEN 5 THEN 'No Show'
		END AS [Status Description]
	   ,sHRE.bigint_timestamp AS [RowId]
	FROM [dbo].[stg$Hotel Res_ Entry] sHRE
	LEFT JOIN [dbo].[stg$Hotel Reservation] sHR
		ON sHRE.CompanyPrefix = sHR.CompanyPrefix
		AND sHRE.[Reservation No_] = sHR.[Reservation No_]
	LEFT JOIN tLocation dLoc
		ON sHRE.Property = dLoc.LocationCode
		AND sHRE.CompanyPrefix = dLoc.CompanyPrefix
	LEFT JOIN tHotelRooms tHR
		ON sHR.[Room No_] = tHR.Room
		AND sHR.CompanyPrefix = tHR.CompanyPrefix
		AND sHR.Property = tHR.[Property]
	LEFT JOIN tHotelRoomsType tHRT
		ON sHR.[CompanyPrefix] = tHRT.CompanyPrefix
		AND sHR.[Room Type] = tHRT.[Room Type]
	LEFT JOIN tPHotelRoomsType tPHRT --Added JI
		ON sHRE.[CompanyPrefix] = tPHRT.CompanyPrefix
		AND sHR.[Paying Room Type] = tPHRT.[Room Type]
		AND sHRE.CompanyPrefix = tPHRT.CompanyPrefix
	LEFT JOIN DW.dCompany dCOM
		ON sHRE.CompanyPrefix = dCOM.CompanyPrefix
	LEFT JOIN tCustomer tCUST
		ON sHR.[Customer No_] = tCUST.CustomerNo
		AND sHR.[CompanyPrefix] = tCUST.CompanyPrefix
	WHERE sHR.[Internal Status] != 0
	AND sHRE.[Reservation No_] in (SELECT [Reservation No_] from tReservationsChangedList)),

tHotelReservationEntryRanked
AS
(SELECT
		*
	   ,ROW_NUMBER() OVER (PARTITION BY [SK_Company], [Reservation No_], [Entry No_] ORDER BY [RowID] DESC) AS [Rank]
	FROM tHotelReservationEntry)

MERGE [DW].[fHotelReservationEntry] AS TARGET USING (SELECT
		tHRR.[SK_Company]
	   ,tHRR.[Date]
	   ,tHRR.[Reservation No_]
	   ,tHRR.[Entry No_]
	   ,tHRR.[Arrival Date]
	   ,tHRR.[Departure Date]
	   ,tHRR.[Booking Date]
	   ,tHRR.[Cancelation DateTime]
	   ,tHRR.[DaysFromBookingToArrivalOrCancel]
	   ,tHRR.[SK_Customer]
	   ,tHRR.[SK_Location]
	   ,tHRR.[SK_HotelRooms]
	   ,tHRR.[SK_PayHotelRooms]--Added JI
	   ,tHRR.[No_ of Rooms]
	   ,tHRR.[Currency]
	   ,tHRR.[Country]
	   ,tHRR.[Market Segment]
	   ,tHRR.[Reservation Channel]
	   ,tHRR.[Reservation Type Description]
	   ,tHRR.[Status]
	   ,tHRR.[Status Description]
	FROM tHotelReservationEntryRanked tHRR
	WHERE tHRR.[rank] = 1) AS SOURCE
ON TARGET.[SK_Company] = SOURCE.SK_Company
	AND TARGET.[Reservation No_] = SOURCE.[Reservation No_]
	AND TARGET.[Entry No_] = SOURCE.[Entry No_]
WHEN MATCHED
	THEN UPDATE
		SET [Date] = SOURCE.[Date]
		   ,[Arrival Date] = SOURCE.[Arrival Date]
		   ,[Departure Date] = SOURCE.[Departure Date]
		   ,[Booking Date] = SOURCE.[Booking Date]
		   ,[Cancelation DateTime] = SOURCE.[Cancelation DateTime]
		   ,[DaysFromBookingToArrivalOrCancel] = SOURCE.[DaysFromBookingToArrivalOrCancel]
		   ,[SK_Customer] = SOURCE.[SK_Customer]
		   ,[SK_Location] = SOURCE.[SK_Location]
		   ,[SK_HotelRooms] = SOURCE.[SK_HotelRooms]
		   ,[SK_PayHotelRooms] = SOURCE.[SK_PayHotelRooms]--Added JI
		   ,[No_ of Rooms] = SOURCE.[No_ of Rooms]
		   ,[Currency] = SOURCE.[Currency]
		   ,[Country] = SOURCE.[Country]
		   ,[Market Segment] = SOURCE.[Market Segment]
		   ,[Reservation Channel] = SOURCE.[Reservation Channel]
		   ,[Reservation Type Description] = SOURCE.[Reservation Type Description]
		   ,[Status] = SOURCE.[Status]
		   ,[Status Description] = SOURCE.[Status Description]
WHEN NOT MATCHED BY TARGET
	THEN INSERT ([SK_Company]
		, [Date]
		, [Reservation No_]
		, [Entry No_]
		, [Arrival Date]
		, [Departure Date]
		, [Booking Date]
		, [Cancelation DateTime]
		, [DaysFromBookingToArrivalOrCancel]
		, [SK_Customer]
		, [SK_Location]
		, [SK_HotelRooms]
		, [SK_PayHotelRooms]--Added JI
		, [No_ of Rooms]
		, [Currency]
		, [Country]
		, [Market Segment]
		, [Reservation Channel]
		, [Reservation Type Description]
		, [Status]
		, [Status Description])
			VALUES (SOURCE.[SK_Company], SOURCE.[Date], SOURCE.[Reservation No_], SOURCE.[Entry No_], SOURCE.[Arrival Date], SOURCE.[Departure Date], SOURCE.[Booking Date], SOURCE.[Cancelation DateTime], SOURCE.[DaysFromBookingToArrivalOrCancel], SOURCE.[SK_Customer], SOURCE.[SK_Location], SOURCE.[SK_HotelRooms], SOURCE.[SK_PayHotelRooms], SOURCE.[No_ of Rooms], SOURCE.[Currency], SOURCE.[Country], SOURCE.[Market Segment], SOURCE.[Reservation Channel], SOURCE.[Reservation Type Description], SOURCE.[Status], SOURCE.[Status Description])
;
END

SELECT
	'RowCount' = @@rowcount
END
GO

/****** Object:  StoredProcedure [dbo].[factHotelRoomBlockedEntry]    Script Date: 20-Jan-23 10:15:38 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





-- =============================================
-- Author:		Gisli Olafsson
-- Modified Date: 2022-12-19
-- Description:	SP for hotels blocked rooms report
-- =============================================

CREATE PROCEDURE [dbo].[factHotelRoomBlockedEntry] 

	@CurrentRowId BIGINT,
	@NewRowId BIGINT,
	@BlockRowId BIGINT,
	@NewBlockRowId BIGINT

AS
BEGIN
	SET NOCOUNT ON;
	-- Dismiss selecting from source if no rows will be fetched
	IF (SELECT
				COUNT(*)
			FROM [stg$Room Type Block] sRTB
			WHERE sRTB.bigint_timestamp BETWEEN @BlockRowId AND @NewBlockRowId)
		= 0
	OR (SELECT
			COUNT(*)
		FROM [stg$Room Blocking Entry] sRTB
		WHERE sRTB.bigint_timestamp BETWEEN @CurrentRowId AND @NewRowId)
	= 0
	BEGIN
		SELECT
			'RowCount' = @@rowcount
	END
	ELSE
	BEGIN

		WITH E00 (N)
		AS
		(SELECT
				1
			UNION ALL
			SELECT
				1),
		E02 (N)
		AS
		(SELECT
				1
			FROM E00 a
				,E00 b),
		E04 (N)
		AS
		(SELECT
				1
			FROM E02 a
				,E02 b),
		E08 (N)
		AS
		(SELECT
				1
			FROM E04 a
				,E04 b),
		E16 (N)
		AS
		(SELECT
				1
			FROM E08 a
				,E08 b),
		E32 (N)
		AS
		(SELECT
				1
			FROM E16 a
				,E16 b),
		cteTally (N)
		AS
		(SELECT
				ROW_NUMBER() OVER (ORDER BY (SELECT
						NULL)
				)
			FROM E32),
		/*
		 Get the list of companies used
		 */
		tCompanies
		AS
		(SELECT
				dCOM.[SK_Company] AS [Company]
			   ,dCOM.[CompanyPrefix]
			FROM [DW].[dCompany] dCOM
			WHERE dCOM.[SK_Company] <> -1),
		/*
		 Set up dimensions and support tables used with the main data process
		 */

		/*Added Jónheiður*/
		tHotelRooms
		AS
		(SELECT
				dHR.[Room Type]
			   ,dHR.[Room]
			   ,dHR.[Property]
			   ,tCOM.CompanyPrefix
			   ,dHR.SK_HotelRooms
			FROM DW.dHotelRooms dHR
			LEFT OUTER JOIN [tCompanies] tCOM
				ON dHR.[Company] = tCOM.[Company]),

		tHotelRoomsType
		AS
		(SELECT
				dHR.[Room Type]
			   ,tCOM.CompanyPrefix
			   ,dHR.SK_HotelRooms
			FROM DW.dHotelRooms dHR
			LEFT OUTER JOIN [tCompanies] tCOM
				ON dHR.[Company] = tCOM.[Company]
			WHERE dHR.[Room] = dHR.[Room Type]),



		tCustomer
		AS
		(SELECT
				dCUST.[SK_Customer]
			   ,tCOM.CompanyPrefix
			   ,dCUST.[CustomerNo]
			FROM [DW].[dCustomer] dCUST
			LEFT OUTER JOIN tCompanies tCOM
				ON dCUST.[Company] = tCOM.[Company]),



		tLocation
		AS
		(SELECT
				dLOC.[SK_Location]
			   ,tCOM.[CompanyPrefix]
			   ,dLOC.[LocationCode]
			FROM DW.[dLocation] dLOC
			LEFT OUTER JOIN [tCompanies] tCOM
				ON dLOC.[Company] = tCOM.[Company]),


		tRoomTypeBlocked
		AS
		(SELECT
				dCOM.SK_Company
			   ,tCUST.SK_Customer
			   ,COALESCE(dLoc.SK_Location, -1) AS SK_Location
			   ,COALESCE(tHRT.SK_HotelRooms, -1) AS SK_HotelRooms
			   ,sRTB.[Entry No_]
			   ,CAST(sRTB.[Blocked from] AS DATE) AS [Blocked from]
			   ,CAST(sRTB.[Blocked to] AS DATE) AS [Blocked to]
			   ,1 AS [Active]
				--,CAST(sRTB.[$systemCreatedAt] AS DATE) AS [Booking Date]
			   ,CAST(sRTB.[Release DateTime] AS DATE) AS [Release DateTime]
			   ,sRTB.[Quantity]
			   ,sRTB.[Channel Type]
			   ,'RTB' AS [Source]
			   ,sRTB.bigint_timestamp AS [RowId]
			FROM [dbo].[stg$Room Type Block] sRTB
			LEFT JOIN tLocation dLoc
				ON sRTB.Property = dLoc.LocationCode
				AND sRTB.CompanyPrefix = dLoc.CompanyPrefix
			LEFT JOIN tHotelRoomsType tHRT
				ON sRTB.[CompanyPrefix] = tHRT.CompanyPrefix
				AND sRTB.[Room Type] = tHRT.[Room Type]
				AND sRTB.CompanyPrefix = tHRT.CompanyPrefix
			LEFT JOIN DW.dCompany dCOM
				ON sRTB.CompanyPrefix = dCOM.CompanyPrefix
			LEFT JOIN tCustomer tCUST
				ON sRTB.[Customer No_] = tCUST.CustomerNo
				AND sRTB.[CompanyPrefix] = tCUST.CompanyPrefix
				AND sRTB.[bigint_timestamp] BETWEEN @BlockRowId AND @NewBlockRowId),

		/*Added by Jónheiður*/
		tRoomBlockingEntry
		AS
		(SELECT
				dCOM.SK_Company
			   ,-1 AS [SK_Customer]
			   ,COALESCE(dLoc.SK_Location, -1) AS SK_Location
			   ,COALESCE(tHRO.SK_HotelRooms, -1) AS SK_HotelRooms --Room number
			   ,sRBE.[Entry No_]
			   ,CAST(sRBE.[Blocked from] AS DATE) AS [Blocked from]
			   ,CAST(sRBE.[Blocked to] AS DATE) AS [Blocked to]
			   ,sRBE.[Active]
				--,CAST(sRTB.[$systemCreatedAt] AS DATE) AS [Booking Date]
			   ,sRBE.[Blocked to] AS [Release DateTime]
			   ,1 AS [Quantity]
			   ,-1 AS [Channel Type]
			   ,'RBE' AS [Source]
			   ,sRBE.bigint_timestamp AS [RowId]
			FROM [dbo].[stg$Room Blocking Entry] sRBE
			LEFT JOIN tLocation dLoc
				ON sRBE.Property = dLoc.LocationCode
				AND sRBE.CompanyPrefix = dLoc.CompanyPrefix
			LEFT JOIN tHotelRooms tHRO
				ON sRBE.[Room No_] = tHRO.[Room]
				AND sRBE.[Property] = tHRO.[Property]
				AND sRBE.CompanyPrefix = tHRO.CompanyPrefix
			LEFT JOIN DW.dCompany dCOM
				ON sRBE.CompanyPrefix = dCOM.CompanyPrefix
				AND sRBE.[bigint_timestamp] BETWEEN @CurrentRowId AND @NewRowId),

		tRoomTypeBlockedRanked
		AS
		(SELECT
				*
			   ,ROW_NUMBER() OVER (PARTITION BY [SK_Company], [SK_Location], [Entry No_] ORDER BY [RowID] DESC) AS [Rank]
			FROM tRoomTypeBlocked),

		/*Added JI*/
		tRoomBlockingEntryRanked
		AS
		(SELECT
				*
			   ,ROW_NUMBER() OVER (PARTITION BY [SK_Company], [SK_Location], [Entry No_] ORDER BY [RowID] DESC) AS [Rank]
			FROM tRoomBlockingEntry),

		tRoomTypeBlockedExploded
		AS
		(SELECT
				tRTB.*
			   ,ExplodedDate = DATEADD(dd, t.number - 1, tRTB.[Blocked from])
			FROM tRoomTypeBlockedRanked tRTB
			CROSS APPLY (SELECT TOP (DATEDIFF(dd, tRTB.[Blocked from], tRTB.[Blocked to]) )
					N AS 'Number'
				FROM cteTally) t),

		tRoomBlockedEntryExploded
		AS
		(SELECT
				tRBE.*
			   ,ExplodedDate = DATEADD(dd, t.number - 1, tRBE.[Blocked from])
			FROM tRoomBlockingEntryRanked tRBE
			CROSS APPLY (SELECT TOP (DATEDIFF(dd, tRBE.[Blocked from], tRBE.[Blocked to]) )
					N AS 'Number'
				FROM cteTally) t),

		tRoomBlockedEntryUnion
		AS
		(SELECT
				*
			FROM tRoomTypeBlockedExploded
			WHERE [rank] = 1
			UNION
			SELECT
				*
			FROM tRoomBlockedEntryExploded
			WHERE [rank] = 1)

		MERGE [DW].[fHotelRoomBlockedEntry] AS TARGET USING (SELECT
				tRBEU.[SK_Company]
			   ,tRBEU.[SK_Customer]
			   ,tRBEU.[SK_Location]
			   ,tRBEU.[SK_HotelRooms]
			   ,tRBEU.[Entry No_]
			   ,tRBEU.[Blocked from]
			   ,tRBEU.[Blocked to]
			   ,tRBEU.[Active]
			   ,tRBEU.[Release DateTime]
			   ,tRBEU.[Quantity]
			   ,tRBEU.[Channel Type]
			   ,tRBEU.[SOURCE]
			   ,tRBEU.[ExplodedDate]
			FROM tRoomBlockedEntryUnion tRBEU) AS SOURCE
		ON TARGET.[SK_Company] = SOURCE.[SK_Company]
			AND TARGET.[Entry No_] = SOURCE.[Entry No_]
			AND TARGET.[SOURCE] = SOURCE.[SOURCE]
			AND TARGET.[ExplodedDate] = SOURCE.[ExplodedDate]
		WHEN MATCHED
			THEN UPDATE
				SET [SK_Customer] = SOURCE.[SK_Customer]
				   ,[SK_Location] = SOURCE.[SK_Location]
				   ,[SK_HotelRooms] = SOURCE.[SK_HotelRooms]
				   ,[Blocked from] = SOURCE.[Blocked from]
				   ,[Blocked to] = SOURCE.[Blocked to]
				   ,[Active] = SOURCE.[Active]
				   ,[Release DateTime] = SOURCE.[Release DateTime]
				   ,[Quantity] = SOURCE.[Quantity]
				   ,[Channel Type] = SOURCE.[Channel Type]
		WHEN NOT MATCHED BY TARGET
			THEN INSERT ([SK_Company]
				, [SK_Customer]
				, [SK_Location]
				, [SK_HotelRooms]
				, [Entry No_]
				, [Blocked from]
				, [Blocked to]
				, [Release DateTime]
				, [Quantity]
				, [Channel Type]
				, [SOURCE]
				, [ExplodedDate]
				, [Active])
					VALUES (SOURCE.[SK_Company], SOURCE.[SK_Customer], SOURCE.[SK_Location], SOURCE.[SK_HotelRooms], SOURCE.[Entry No_], SOURCE.[Blocked from], SOURCE.[Blocked to], SOURCE.[Release DateTime], SOURCE.[Quantity], SOURCE.[Channel Type], SOURCE.[SOURCE], SOURCE.[ExplodedDate], SOURCE.[Active])
		WHEN NOT MATCHED BY SOURCE
			THEN DELETE
		;
	END

	SELECT
		'RowCount' = @@rowcount
END
GO

/****** Object:  StoredProcedure [dbo].[factHotelsRoomAvailability]    Script Date: 20-Jan-23 10:15:48 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:		Gisli Olafsson
-- Modified Date: 2022-12-06
-- Description:	SP for hotel rooms availability
-- =============================================

CREATE PROCEDURE [dbo].[factHotelsRoomAvailability] 

	@CurrentRowId BIGINT,
	@NewRowId BIGINT

AS
BEGIN
	SET NOCOUNT ON;
	-- Dismiss selecting from source if no rows will be fetched
	IF (SELECT
				COUNT(*)
			FROM [stg$Hotel Room Availability] sHRA
			WHERE sHRA.bigint_timestamp BETWEEN @CurrentRowId AND @NewRowId)
		= 0
	BEGIN
		SELECT
			'RowCount' = @@rowcount
	END
	ELSE
	BEGIN
		WITH

		/*
   	  Get the list of companies used
   	  */
		tCompanies
		AS
		(SELECT
				dCOM.[SK_Company] AS [Company]
			   ,dCOM.[CompanyPrefix]
			FROM [DW].[dCompany] dCOM
			WHERE dCOM.[SK_Company] <> -1),
		/*
   	  Set up dimensions and support tables used with the main data process
   	  */

		tLocation
		AS
		(SELECT
				dLOC.[SK_Location]
			   ,tCOM.[CompanyPrefix]
			   ,dLOC.[LocationCode]
			FROM DW.[dLocation] dLOC
			LEFT OUTER JOIN [tCompanies] tCOM
				ON dLOC.[Company] = tCOM.[Company]),

		tHotelRoomsType
		AS
		(SELECT
				dHR.[Room Type]
			   ,tCOM.CompanyPrefix
			   ,dHR.SK_HotelRooms
			FROM DW.dHotelRooms dHR
			LEFT OUTER JOIN [tCompanies] tCOM
				ON dHR.[Company] = tCOM.[Company]
			WHERE dHR.[Room] = dHR.[Room Type]),

		tHotelRoomAvailability
		AS
		(SELECT
				dCOM.SK_Company
			   ,sHRA.[bigint_timestamp] AS [RowId]
			   ,CAST(sHRA.[Date] AS DATE) AS [Date]
			   ,tHRT.SK_HotelRooms AS [SK_HotelRooms]
			   ,COALESCE(dLoc.SK_Location, -1) AS SK_Location
			   ,sHRA.[Total Rooms]
			   ,sHRA.[Available Rooms]
			   ,sHRA.[Blocked Rooms]
			FROM [dbo].[stg$Hotel Room Availability] sHRA
			LEFT JOIN tLocation dLoc
				ON sHRA.Property = dLoc.LocationCode
				AND sHRA.CompanyPrefix = dLoc.CompanyPrefix
			LEFT JOIN tHotelRoomsType tHRT
				ON sHRA.[CompanyPrefix] = tHRT.CompanyPrefix
				AND sHRA.[Room Type] = tHRT.[Room Type]
			LEFT JOIN DW.dCompany dCOM
				ON sHRA.CompanyPrefix = dCOM.CompanyPrefix
				WHERE sHRA.[bigint_timestamp] BETWEEN @CurrentRowId AND @NewRowId),

		tHotelRoomAvailabilityRanked
		AS
		(SELECT
				*
			   ,ROW_NUMBER() OVER (PARTITION BY [SK_Company], [SK_HotelRooms], [SK_Location], [Date] ORDER BY [RowID] DESC) AS [Rank]
			FROM tHotelRoomAvailability)

		MERGE [DW].[fHotelRoomAvailability] AS TARGET USING (SELECT
				tHRAR.SK_Company
			   ,tHRAR.Date
			   ,tHRAR.SK_HotelRooms
			   ,tHRAR.SK_Location
			   ,tHRAR.[Total Rooms]
			   ,tHRAR.[Available Rooms]
			   ,tHRAR.[Blocked Rooms]
			FROM tHotelRoomAvailabilityRanked tHRAR
			WHERE [rank] = 1) AS SOURCE
		ON TARGET.SK_Company = SOURCE.SK_Company
			AND TARGET.Date = SOURCE.Date
			AND TARGET.SK_HotelRooms = SOURCE.SK_HotelRooms
			AND TARGET.SK_Location = SOURCE.SK_Location
		WHEN MATCHED
			THEN UPDATE
				SET [Total Rooms] = SOURCE.[Total Rooms]
				   ,[Available Rooms] = SOURCE.[Available Rooms]
				   ,[Blocked Rooms] = SOURCE.[Blocked Rooms]
		WHEN NOT MATCHED BY TARGET
			THEN INSERT ([SK_Company]
				, [Date]
				, [SK_HotelRooms]
				, [SK_Location]
				, [Total Rooms]
				, [Available Rooms]
				, [Blocked Rooms])
					VALUES (SOURCE.[SK_Company], SOURCE.[Date], SOURCE.[SK_HotelRooms], SOURCE.[SK_Location], SOURCE.[Total Rooms], SOURCE.[Available Rooms], SOURCE.[Blocked Rooms])
		;
	END

	SELECT
		'RowCount' = @@rowcount
END
GO


/****** Object:  StoredProcedure [dbo].[dimextMergedHotelRooms]    Script Date: 20-Jan-23 3:20:59 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE PROCEDURE [dbo].[dimextMergedHotelRooms] 
AS
 IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Stg$Hotel Rooms')
BEGIN

WITH tHotelRooms
AS
(SELECT
		sHRO.[CompanyPrefix]
	   ,sHRO.[Room]
	   ,sHRO.[Room Type]
	   ,sHRO.[Description]
	   ,sHRO.[Building]
	   ,sHRO.[Floor]
	   ,sHRO.[Property]
	   ,sHRO.[Wing]
	   ,sHRO.[bigint_timestamp] AS [RowID]
	   ,ROW_NUMBER() OVER (PARTITION BY sHRO.[CompanyPrefix], sHRO.[Room], sHRO.[Property] ORDER BY [bigint_timestamp] DESC) AS [Rank]
	   ,GETUTCDATE() AS [BatchDate]
	FROM [Stg$Hotel Rooms] sHRO),

tHotelRoomTypes 
AS
(SELECT sHRO.[CompanyPrefix]
	   ,sHRO.[Room Type] as [Room]
	   ,sHRO.[Room Type]
	   ,sHRO.[Description] AS [Description]
	   ,' ' AS [Building]
	   ,' ' AS [Floor]
	   ,' ' AS [Property]
	   ,' ' AS [Wing]
	   ,' ' AS [RowID]
	   ,ROW_NUMBER() OVER (PARTITION BY sHRO.[CompanyPrefix], sHRO.[Room Type] ORDER BY [Room Type] DESC) AS [Rank]
	   ,GETUTCDATE() AS [BatchDate]
	   FROM [Stg$Hotel Rooms] sHRO
	   GROUP BY sHRO.[CompanyPrefix]
		,sHRO.[Room Type]
		,sHRO.[Description]),

tHotelRoomsUnion
AS
(SELECT * FROM tHotelRooms
	UNION ALL
SELECT * FROM tHotelRoomTypes)



MERGE [DW].[dHotelRooms] AS TARGET USING 
(SELECT
		dCOM.[SK_Company] AS [Company]
	   ,tHRO.[Room]
	   ,tHRO.[Room Type]
	   ,tHRO.[Description]
	   ,tHRO.[Building]
	   ,tHRO.[Floor]
	   ,tHRO.[Property]
	   ,tHRO.[Wing]
	   ,tHRO.[RowID]
	   ,tHRO.[Rank]
	   ,tHRO.[BatchDate]
	FROM tHotelRoomsUnion tHRO
	LEFT JOIN [DW].[dCompany] dCOM
		ON dCOM.[CompanyPrefix] = tHRO.[CompanyPrefix]
	WHERE tHRO.[Rank] = 1
) AS Source

ON TARGET.[Room] = Source.[Room]
	AND TARGET.[Company] = Source.[Company]
	AND TARGET.[Property] = Source.[Property]

WHEN MATCHED
	THEN UPDATE
		SET [Room Type] = Source.[Room Type]
		   ,[Description] = Source.[Description]
		   ,[Building] = Source.[Building]
		   ,[Floor] = Source.[Floor]
		   ,[Wing] = Source.[Wing]
		   ,[RowID] = Source.[RowID]
		   ,[BatchDate] = Source.[BatchDate]
WHEN NOT MATCHED BY TARGET
	THEN INSERT ([Company], [Room], [Room Type], [Description], [Building], [Floor], [Property], [Wing], [RowID], [BatchDate])
			VALUES (Source.[Company], Source.[Room], Source.[Room Type], Source.[Description], Source.[Building], Source.[Floor], Source.[Property], Source.[Wing], Source.[RowID], Source.[BatchDate])

WHEN NOT MATCHED BY SOURCE
	AND TARGET.[SK_HotelRooms] <> -1
	THEN DELETE;
;
END

SELECT
	'RowCount' = @@rowcount
GO




